﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Collections;
using System.Configuration;
using System.IO;
using System.Data.Common;


namespace DAO
{
    public class Connection
    {
        #region Filter Para

        public void AddFilterParameter(List<DTO.FilterParameter> filters)
        {
            if (filters != null && filters.Count > 0)
            {
                for (int i = 0; i < filters.Count; i++)
                {
                    if (filters[i].value is object[])
                    {
                        object[] tmp = filters[i].value as object[];
                        int len = tmp.Length;
                        if (filters[i].method.ToLower() == "between") len = 2;
                        for (int j = 0; j < len; j++)
                        {
                            AddParameter(string.Format("{0}{1}{2}", filters[i].prefixVal, filters[i].name, j), tmp[j]);
                        }
                    }
                    else
                    {
                        AddParameter(string.Format("{0}{1}", filters[i].prefixVal, filters[i].name), filters[i].value);
                    }
                }
            }
        }
        protected virtual bool BeginFilterParameter(ref string sql, List<DTO.FilterParameter> filters, string fistCondition = "AND")
        {
            if (filters != null && filters.Count > 0)
            {
                sql += string.Format(" {0} ", fistCondition);
                for (int i = 0; i < filters.Count; i++)
                {
                    if (i != 0) sql += " AND ";
                    sql += filters[i].ToFilterString();
                }
                return true;
            }
            return false;
        }
        #endregion

        #region Local Variable
        const string MinSqlServerVersion = "11.0.2100.60";
        enum ProviderType
        {
            Oracle, SqlServer, OleDb, Odbc, MySql
        }
        class DbInstance
        {
            public ProviderType _type = ProviderType.SqlServer;
            public int _timeout = 500;
            public string _connectionString = "";
            public DbProviderFactory _factory;
            public DbConnection _connection;
            public DbDataAdapter _adapter;
            public DbCommand _command;
            public DbTransaction _trans;
        }
        string _type = "S";
        DbInstance _dbInstance = null;

        public string DbType
        {
            get
            {
                return GetStringType(_dbInstance._type);
            }
            set
            {
                var tmp = GetProviderType(value);
                if (value != _type) _type = value;
            }
        }

        private ProviderType GetProviderType(string type)
        {
            if (type == "S" || type == "SqlServer" || type == "System.Data.SqlClient") return ProviderType.SqlServer;
            if (type == "M" || type == "MySql" || type == "MySql.Data.MySqlClient") return ProviderType.MySql;
            if (type == "O" || type == "Oracle" || type == "MySql.Data.OracleClient") return ProviderType.Oracle;
            if (type == "OleDb" || type == "MySql.Data.OleDb") return ProviderType.OleDb;

            return ProviderType.Odbc;
        }

        private string GetStringType(ProviderType providerType)
        {
            if (providerType == ProviderType.SqlServer) return "S";
            if (providerType == ProviderType.MySql) return "M";
            if (providerType == ProviderType.Oracle) return "O";
            if (providerType == ProviderType.OleDb) return "OleDb";
            return "Odbc";
        }
        public int Timeout { get { return _dbInstance._timeout; } set { if (value != _dbInstance._timeout) _dbInstance._timeout = value; } }
        public string ConnectionString { get { return _dbInstance._connectionString; } set { if (value != _dbInstance._connectionString) _dbInstance._connectionString = value; } }
        protected DbConnection _connection { get { return _dbInstance._connection; } set { if (value != _dbInstance._connection) _dbInstance._connection = value; } }
        protected DbDataAdapter _adapter { get { return _dbInstance._adapter; } set { if (value != _dbInstance._adapter) _dbInstance._adapter = value; } }
        protected DbCommand _command { get { return _dbInstance._command; } set { if (value != _dbInstance._command) _dbInstance._command = value; } }
        protected DbTransaction _trans { get { return _dbInstance._trans; } set { if (value != _dbInstance._trans) _dbInstance._trans = value; } }
        protected DbProviderFactory _factory { get { return _dbInstance._factory; } set { if (value != _dbInstance._factory) _dbInstance._factory = value; } }
        #endregion Local Variable

        #region Initialize
        public Connection(Connection connection)
        {
            //DbProviderFactory dbPro = DbProviderFactories.GetFactory(connection._dbInstance._connection);

            // this = connection;
            ChangeConnection(connection);
        }
        public Connection(string type, string connectString, int timeout = 0)
        {
            ChangeConnection(type, connectString, timeout);
        }
        #endregion Initialize

        #region Connection Method
        public void ChangeConnection(Connection connection)
        {
            _dbInstance = connection._dbInstance;
        }
        public void ChangeConnection(string type, string connectString, int timeout = 0)
        {
            _dbInstance = new DbInstance();
            DbType = type;
            Timeout = timeout;
            ConnectionString = connectString;
            _connection = null;
            _adapter = null;
            _trans = null;

            InitConnect();

        }
        public static string getConnectString(string type, string server, string uid, string pwd, string dbname = "", int timeout = 0, int port = 0)
        {
            if (server == "") return "";
            if (type == "S" || type == "MsSql" || type == "System.Data.SqlClient")
            {
                string strport = "";
                string strdb = "";
                if (dbname != "")
                    strdb = string.Format("database={0};", dbname);
                if (port != 0)
                    strport = string.Format(",{0}", port);
                return string.Format("Server={0}{4};uid={1};pwd={2};{3}", server, uid, pwd, strdb, strport);
            }
            else if (type == "M" || type == "MySql" || type == "MySql.Data.MySqlClient")
            {
                string strport = "";
                string strdb = "";
                if (dbname != "")
                    strdb = string.Format("database={0};", dbname);
                if (port != 0)
                    strport = string.Format("port={0};", port);
                return string.Format("Server={0};uid={1};pwd={2};{3}{4}", server, uid, pwd, strdb, strport);
            }

            if (type == "O") return "";

            return "";
        }

        protected void InitConnect()
        {
            //            Activator.CreateInstance(Type.GetType("System.Data.SqlClient"));
            CreateFactory(_dbInstance._type);
            if (_connection == null) CreateConnection(ConnectionString);
            if (_command == null || _command.Connection != _connection) _command = _connection.CreateCommand();
            if (_adapter == null || _adapter.SelectCommand != _command) _adapter = CreateAdapter(_command);

            // Get SQL database version
            /*   if ((DbType == "S" || DbType == "MsSql" || DbType == "System.Data.SqlClient") && SqlServerVersion == null)
               {
                   try
                   {
                       if (_connection.State == ConnectionState.Closed || _connection.State == ConnectionState.Broken) _connection.Open();
                       SqlServerVersion = _connection.ServerVersion;
                   }
                   finally {}

                   //InitCommand("SELECT SERVERPROPERTY('productversion')");
                   //try
                   //{
                   //    DataTable dt = executeSelect();
                   //    if (dt.Rows.Count == 1) SqlServerVersion = dt.Rows[0][0].ToString();
                   //}
                   //catch { }
               }
             * */

        }

        private void CreateFactory(ProviderType type)
        {
            if (type == ProviderType.SqlServer) _factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
            else if (type == ProviderType.MySql) _factory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
            else if (type == ProviderType.Oracle) _factory = DbProviderFactories.GetFactory("System.Data.OracleClient");
            else if (type == ProviderType.OleDb) _factory = DbProviderFactories.GetFactory("System.Data.OleDb");
            else _factory = DbProviderFactories.GetFactory("System.Data.Odbc");
        }
        private void CreateConnection(string connectString)
        {
            _connection = _factory.CreateConnection();
            _connection.ConnectionString = connectString;
        }

        private DbDataAdapter CreateAdapter(DbCommand a_command)
        {
            DbDataAdapter res = _factory.CreateDataAdapter();
            res.SelectCommand = a_command;

            return res;
        }

        protected virtual void InitCommand(string strCommandText, CommandType commandType = CommandType.Text)
        {
            if (_command == null) _connection.CreateCommand();

            _command.CommandType = commandType;
            _command.CommandText = strCommandText;
            _command.CommandTimeout = Timeout;
            _command.Parameters.Clear();

            _adapter = CreateAdapter(_command);

        }
        public bool TestConnect()
        {
            string sErr = "";
            bool flag = true;
            try
            {
                openConnection();
                closeConnection();
            }
            catch (DbException ex)
            {
                flag = false;
                sErr = ex.Message;
            }
            return flag;
        }
        public virtual void openConnection()
        {
            InitConnect();
            if (_connection.State == ConnectionState.Broken || _connection.State == ConnectionState.Closed) _connection.Open();
        }
        public virtual void closeConnection()
        {
            if (_trans != null) return;
            if (_connection.State != ConnectionState.Broken && _connection.State != ConnectionState.Closed)
                _connection.Close();

        }
        #endregion Connection Method

        #region Transaction
        public virtual object BeginTransaction(ref String sErr)
        {
            try
            {
                openConnection();
                _trans = _connection.BeginTransaction();
                _command.Transaction = _trans;
                return _trans;
            }
            catch (DbException ex)
            {
                sErr = ex.Message;
                return null;
            }
        }
        public virtual bool CommitTransaction(ref String sErr)
        {
            if (_trans == null) return true;

            try
            {
                _trans.Commit();
                _trans = null;
                closeConnection();
                return true;
            }
            catch (DbException ex)
            {
                sErr = ex.Message;
                _trans.Rollback();
                _trans = null;
                closeConnection();
            }
            finally
            {
                _connection.Close();
            }
            return false;
        }
        public virtual bool RollbackTransaction(ref String sErr)
        {
            if (_trans == null) return true;

            try
            {
                _trans.Rollback();
                _trans = null;
                closeConnection();
                return true;
            }
            catch (DbException ex)
            {
                sErr = ex.Message;
                _trans = null;
                closeConnection();
            }
            finally
            {
                _connection.Close();
            }
            return false;
        }
        #endregion Transaction

        #region Additional Method
        protected virtual void AddParameter(string name, object value)
        {
            if (_command.Parameters.IndexOf(name) != -1) return;

            DbParameter para = _command.CreateParameter();
            para.ParameterName = name;
            para.Value = value;
            _command.Parameters.Add(para);
        }

        protected virtual void AddParameter(string name, object value, DbType type)
        {
            if (_command.Parameters.IndexOf(name) != -1) return;

            DbParameter para = _command.CreateParameter();
            para.ParameterName = name;
            para.Value = value;
            para.DbType = type;
            _command.Parameters.Add(para);
        }

        //public static string Image2Base64(Image image, System.Drawing.Imaging.ImageFormat format)
        //{
        //    using (MemoryStream ms = new MemoryStream())
        //    {
        //        // Convert Image to byte[]
        //        image.Save(ms, format);
        //        byte[] imageBytes = ms.ToArray();

        //        // Convert byte[] to Base64 String
        //        string base64String = Convert.ToBase64String(imageBytes);
        //        return base64String;
        //    }
        //}
        //public static Image Base642Image(string base64String)
        //{
        //    // Convert Base64 String to byte[]
        //    byte[] imageBytes = Convert.FromBase64String(base64String);
        //    MemoryStream ms = new MemoryStream(imageBytes, 0,
        //      imageBytes.Length);

        //    // Convert byte[] to Image
        //    ms.Write(imageBytes, 0, imageBytes.Length);
        //    Image image = Image.FromStream(ms, true);
        //    return image;
        //}

        //protected virtual void AddParameterImage(string name, System.Drawing.Image value)
        //{
        //    string base64String = Image2Base64(value, System.Drawing.Imaging.ImageFormat.Png);
        //    AddParameter(name, base64String);
        //}

        /// <summary>
        /// Chuyển một dòng dữ liệu thành một đối tượng tương ứng với lớp kế thừa
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="i"></param>
        /// <returns></returns>
        /// 
        protected virtual object GetDataFromDataRow(DataTable dt, int i)
        {

            return null;
        }

        protected virtual ArrayList ConvertDataSetToArrayList(DataSet dataset)
        {
            ArrayList arr = new ArrayList();
            DataTable dt = dataset.Tables[0];
            int i, n = dt.Rows.Count;
            for (i = 0; i < n; i++)
            {
                object hs = GetDataFromDataRow(dt, i);
                arr.Add(hs);

            }
            return arr;
        }
        protected virtual ArrayList ConvertDataTableToArrayList(DataTable dt)
        {
            ArrayList arr = new ArrayList();
            int i, n = dt.Rows.Count;
            for (i = 0; i < n; i++)
            {
                object hs = GetDataFromDataRow(dt, i);
                arr.Add(hs);
            }
            return arr;
        }
        protected virtual Object[] ConvertDataSetToArray(DataSet dataset)
        {
            DataTable dt = dataset.Tables[0];
            Object[] arr = new Object[dt.Rows.Count];
            int i, n = dt.Rows.Count;
            for (i = 0; i < n; i++)
            {
                object hs = GetDataFromDataRow(dt, i);
                arr[i] = hs;
            }
            return arr;
        }
        protected virtual Object[] ConvertDataTableToArray(DataTable dt)
        {
            Object[] arr = new Object[dt.Rows.Count];
            int i, n = dt.Rows.Count;
            for (i = 0; i < n; i++)
            {
                object hs = GetDataFromDataRow(dt, i);
                arr[i] = hs;
            }
            return arr;
        }

        public virtual DateTime GetDateSys()
        {
            return DateTime.Now;
        }

        #endregion Additional Method

        #region Execute Query
        public virtual DataTable executeSelectQuery(string sqlString)
        {
            _command = _connection.CreateCommand();
            _command.CommandText = sqlString;
            return executeSelectQuery(_command);
        }

        public virtual DataTable executeSelectQuery(DbCommand command)
        {
            _adapter = CreateAdapter(command);
            return executeSelectCollect().Tables[0];
        }
        public virtual DataTable executeSelect()
        {
            return executeSelectCollect().Tables[0];
        }
        public virtual DataTable executeSelect(DbCommand command)
        {
            return executeSelectCollect(command).Tables[0];
        }

        public virtual DataSet executeSelectCollect(DbCommand command)
        {
            _adapter = CreateAdapter(command);
            return executeSelectCollect();
        }
        public virtual DataSet executeSelectCollect()
        {
            DataSet ds = new DataSet();

            _adapter.Fill(ds);
            return ds;
        }

        public virtual IDataReader executeQuery(string sqlString)
        {
            _command.CommandText = sqlString;
            return executeQuery();
        }

        public virtual IDataReader executeQuery()
        {
            return _command.ExecuteReader();
        }

        public virtual int executeNonQuery(string sqlString)
        {
            _command.CommandText = sqlString;
            return executeNonQuery();
        }
        public virtual int executeNonQuery()
        {
            return _command.ExecuteNonQuery();
        }

        public virtual object executeScalar()
        {
            return _command.ExecuteScalar();
        }
        public virtual object executeScalar(DbCommand command)
        {
            _command = command;
            return executeScalar();
        }
        public virtual object executeScalar(string sqlString)
        {
            _command.CommandText = sqlString;
            return executeScalar();
        }
        #endregion Execute Query

        #region Database Schema
        public virtual DataTable GetDataBases(ref string sErr)
        {
            try
            {
                if (_connection == null)
                {
                    CreateFactory(_dbInstance._type);
                    CreateConnection(ConnectionString);
                }
                return _connection.GetSchema("Databases");
            }
            catch (Exception ex) { sErr = ex.Message; }

            return null;

        }
        public DataTable GetFields(string tablename, ref string sErr)
        {
            string sql = @"SELECT COLUMN_NAME, DATA_TYPE
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_NAME = N'" + tablename + "'";

            DataTable dt = null;

            if (_dbInstance._type == ProviderType.MySql)
            {
                sql += " AND TABLE_SCHEMA = '" + this._dbInstance._connection.Database + "'";
            }

            InitConnect();
            InitCommand(sql);

            try
            {
                dt = executeSelect();
            }
            catch (Exception ex) { sErr = ex.Message + " sql=" + sql; }
            finally { closeConnection(); }

            return dt;

        }
        public DataTable GetTables(string type, List<DTO.FilterParameter> filters, ref string sErr, int indexPage = 0, int itemPerPage = 0)
        {
            string sql = @"SELECT TABLE_NAME
                FROM INFORMATION_SCHEMA.TABLES";

            if (type == "Tables")
            {
                type = "BASE TABLE";
                sql += " WHERE TABLE_TYPE = 'BASE TABLE'";
            }
            else
            {
                type = "VIEW";
                sql += " WHERE TABLE_TYPE = 'VIEW'";
            }

            bool hasFilter = BeginFilterParameter(ref sql, filters);

            DataTable dt = null;

            if (_dbInstance._type == ProviderType.MySql)
            {
                sql += " AND TABLE_SCHEMA = '" + this._dbInstance._connection.Database + "'";
            }

            if (itemPerPage != 0)
            {
                if (_dbInstance._type == ProviderType.SqlServer)
                {
                    if (string.Compare(GetSqlServerVersion(), GetMinSqlServerVersion()) >= 0)
                    {
                        sql += @" ORDER BY TABLE_NAME 
                        OFFSET @indexPage ROWS
                        FETCH NEXT @itemPerPage ROWS ONLY";
                    }
                    else
                    {
                        string fs = "";
                        BeginFilterParameter(ref fs, filters);
                        sql = @"DECLARE @first_id nvarchar(255), @startIndex int;
                        SET @startIndex = @indexPage + 1;
                        SET ROWCOUNT @startIndex;
                        SELECT @first_id = TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = '" + type + @"' " + fs + @" ORDER BY TABLE_NAME;
                        SET ROWCOUNT @itemPerPage;
                        SELECT TABLE_NAME
                        FROM INFORMATION_SCHEMA.TABLES
                        WHERE TABLE_NAME >= @first_id AND TABLE_TYPE = '" + type + @"'" + fs + @"
                        ORDER BY TABLE_NAME;
                        SET ROWCOUNT 0;";
                    }
                }
                else
                {
                    sql += @" ORDER BY TABLE_NAME 
                    LIMIT @indexPage ,@itemPerPage ";
                }
            }

            InitConnect();
            InitCommand(sql);
            if (itemPerPage != 0)
            {
                AddParameter("indexPage", indexPage);
                AddParameter("itemPerPage", itemPerPage);
            }
            if (hasFilter) AddFilterParameter(filters);
            try { dt = executeSelect(); }
            catch (Exception ex) { sErr = ex.Message + " sql=" + sql; }
            finally { closeConnection(); }

            return dt;
        }

        public int GetTableCount(string type, List<DTO.FilterParameter> filters, ref string sErr)
        {
            string sql = @"SELECT COUNT(TABLE_NAME)
                FROM INFORMATION_SCHEMA.TABLES";

            if (type == "Tables")
            {
                type = "BASE TABLE";
                sql += " WHERE TABLE_TYPE = 'BASE TABLE'";
            }
            else
            {
                type = "VIEW";
                sql += " WHERE TABLE_TYPE = 'VIEW'";
            }

            bool hasFilter = BeginFilterParameter(ref sql, filters);

            if (_dbInstance._type == ProviderType.MySql)
            {
                sql += " AND TABLE_SCHEMA = '" + this._dbInstance._connection.Database + "'";
            }

            int ret = 0;
            openConnection();
            InitCommand(sql);
            if (hasFilter) AddFilterParameter(filters);
            try
            {
                object tmp = executeScalar();

                if (tmp != null && tmp != DBNull.Value) ret = Convert.ToInt32(tmp);
                else ret = 0;
            }
            catch (Exception ex) { sErr = ex.Message + " sql=" + sql; }
            finally { closeConnection(); }

            return ret;
        }
        #endregion Database Schema

        public string GetMinSqlServerVersion()
        {
            return MinSqlServerVersion;
        }

        public string GetSqlServerVersion()
        {
            string res = null;
            openConnection();
            try { res = _dbInstance._connection.ServerVersion; }
            finally { closeConnection(); }

            return res;
        }

    }

}
